package com.authcode.easyexcel;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import cn.afterturn.easypoi.exception.excel.ExcelExportException;
import lombok.Getter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.*;

/**
 * @author Mr.yuan
 * @version v1.0
 * @date 2019/8/20 10:13
 **/
public final class ExcelUtils {

    /**
     * 获取目标流文件
     */
    private static InputStream getTemplateInputStream(String fileName) {
        return Thread.currentThread().getContextClassLoader().getResourceAsStream("WEB-INF/template/" + fileName);
    }

    /**
     * 获取模板地址
     */
    private static String getTemplateUrl(String fileName) {
        return "WEB-INF/template/" + fileName;
    }

    /**
     * 模板单sheet导出
     *
     * @param entity 导出参数
     * @author Mr.yuan
     * @date 2019/8/20 17:03
     **/
    public static void templateExport(ExcelTemplateExportEntity entity) throws Exception {
        //传参校验
        validData(entity);
        //查询模板是否存在
        InputStream stream = getTemplateInputStream(entity.getTemplateName());
        if (null == stream) {
            throw new FileNotFoundException("invalid file path");
        }
        //设置基本参数
        TemplateExportParams params = setTemplateExportParams(entity);

        //获取头部数据
        HeaderData data = getHeaderData(entity);

        //自定义处理列数据
        handleCellData(data.getHeadingList());

        //生成模板
        Workbook workbook = ExcelExportUtil.exportExcel(params, data.getDataMap());

        //处理合计
        handleSum(data.getHeadingList(), workbook, Boolean.FALSE);

        //执行下载
        responseExport(entity, workbook);
    }

    /**
     * 通用单sheet导出
     *
     * @param entity 参数实体
     * @author Mr.yuan
     * @date 2019/8/21 16:08
     **/
    public static void export(ExcelExportEntity entity) throws Exception {
        //传参校验
        validData(entity);

        //设置基本参数
        ExportParams params = setExportParams(entity);

        //获取数据
        List list = (List) entity.getData();

        //自定义处理列数据
        handleCellData(list);

        //生成模板
        Workbook workbook = getWorkBook(entity, params, list);

        //设置标题行样式
        int colLength = setHeadingStyle(workbook);

        //重新计算并复制合计行
        handleSum(list, workbook, Boolean.TRUE);

        //设置合计行样式
        setSumStyle(list, workbook, colLength);

        //执行下载
        responseExport(entity, workbook);
    }

    /**
     * @param entity 参数实体
     * @return cn.afterturn.easypoi.excel.entity.ExportParams
     * @author Mr.yuan
     * @date 2019/8/21 16:37
     **/
    private static ExportParams setExportParams(ExcelExportEntity entity) {
        ExportParams params = new ExportParams(entity.getTitle(), entity.getSheetName(), entity.getType());
        params.setSheetName(entity.getSheetName());
        params.setStyle(SmpDefaultExcelExportStyle.class);
        return params;
    }

    /**
     * 自定义处理列数据
     *
     * @param list 数据集
     * @author Mr.yuan
     * @date 2019/8/22 16:33
     **/
    private static void handleCellData(List list) {
        long a = System.currentTimeMillis();
        int threshold = 1000;
        if (list.size() < threshold) {
            list.forEach(obj -> {
                try {
                    handleCellValue(obj);
                } catch (IllegalAccessException e) {
                    throw new RuntimeException(e);

                }
            });
        } else {
            list.parallelStream().forEach(obj -> {
                try {
                    handleCellValue(obj);
                } catch (IllegalAccessException e) {
                    throw new RuntimeException(e);
                }
            });
        }
        long b = System.currentTimeMillis();
        System.out.println("处理列数值耗时：" + (b - a));
    }

    /**
     * 单价、数量去除多余0，有几位显示几位
     */
    private static void handleCellValue(Object obj) throws IllegalAccessException {
        Field[] fields = obj.getClass().getDeclaredFields();
        for (Field field : fields) {
            field.setAccessible(true);
            String type = field.getGenericType().getTypeName();
            if ("java.math.BigDecimal".equals(type)) {
                Object value = field.get(obj);
                //保留8位小数,变相去掉科学计数法
                DecimalFormat df = new DecimalFormat("##0.00000000");
                String value2 = null == value ? BigDecimal.ZERO.toString() : df.format(value);
                if (value2.indexOf(".") > 0) {
                    //去掉多余的0
                    value2 = value2.replaceAll("0+?$", "");
                    //如最后一位是.则去掉
                    value2 = value2.replaceAll("[.]$", "");
                }
                BigDecimal value3 = new BigDecimal(value2);
                field.set(obj, value3);
            }
        }
    }

    /**
     * 生成模板
     *
     * @param entity 参数实体
     * @param params 基本参数
     * @param list   数据集
     * @return org.apache.poi.ss.usermodel.Workbook
     * @author Mr.yuan
     * @date 2019/8/21 16:25
     **/
    private static Workbook getWorkBook(ExcelExportEntity entity, ExportParams params, List list) {
        List dataList = new ArrayList(list);
        //创建工作簿
        Workbook workbook = entity.getType().equals(ExcelType.HSSF) ? new HSSFWorkbook() : new XSSFWorkbook();
        //创建sheet
        ExcelExportService service = new ExcelExportService();
        service.createSheet(workbook, params, entity.getPojoClass(), dataList);
        return workbook;
    }

    /**
     * 设置数据列样式
     *
     * @param workbook 工作簿
     * @return int
     * @author Mr.yuan
     * @date 2019/8/21 16:22
     **/
    private static int setHeadingStyle(Workbook workbook) {
        Sheet sheet = workbook.getSheetAt(0);
        //获取列数
        int colLength = sheet.getRow(0).getLastCellNum();
        //根据标题名自定义列宽
        for (int i = 0; i < colLength; i++) {
            String name = sheet.getRow(0).getCell(i).getStringCellValue();
            sheet.setColumnWidth(i, name.length() * 512 + 4 * 256);
        }
        return colLength;
    }

    /**
     * 设置合计行样式
     *
     * @param list      数据集
     * @param workbook  工作簿
     * @param colLength 列长度
     * @author Mr.yuan
     * @date 2019/8/21 16:18
     **/
    private static void setSumStyle(List list, Workbook workbook, int colLength) {
        Sheet sheet = workbook.getSheetAt(0);
        //判断是否存在合计行
        boolean sum = false;
        Field[] fields = list.get(0).getClass().getDeclaredFields();
        for (Field field : fields) {
            Excel excel = field.getAnnotation(Excel.class);
            if (null != excel && excel.isStatistics()) {
                sum = true;
            }
        }
        if (sum) {
            //获取最后一行
            Row row = sheet.getRow(sheet.getLastRowNum());
            //设置样式
            for (int i = 0; i < colLength; i++) {
                Cell cell = row.getCell(i);
                if (null == cell) {
                    cell = row.createCell(i);
                }
                cell.setCellStyle(sumCellStyle(workbook));
            }
        }
    }


    /**
     * 校验数据
     *
     * @param entity 参数实体
     * @author Mr.yuan
     * @date 2019/8/21 16:01
     **/
    private static void validData(ExcelExportEntity entity) throws Exception {
        if (entity.getTemplate() && StringUtils.isEmpty(entity.getTemplateName())) {
            throw new FileNotFoundException("export param templateName not empty");
        }
        if (!entity.getTemplate() && StringUtils.isEmpty(entity.getExportTemplateName())) {
            throw new FileNotFoundException("export param exportTemplateName not empty");
        }
        if (Objects.isNull(entity.getData())) {
            throw new ExcelExportException("export param data not null");
        }
        if (entity.getData() instanceof List && Objects.isNull(entity.getPojoClass())) {
            throw new ExcelExportException("export param pojoClass not null");
        }
    }

    /**
     * 设置表头参数
     *
     * @param entity 参数实体
     * @return cn.afterturn.easypoi.excel.entity.TemplateExportParams
     * @author Mr.yuan
     * @date 2019/8/20 17:13
     **/
    private static TemplateExportParams setTemplateExportParams(ExcelTemplateExportEntity entity) {
        TemplateExportParams params = new TemplateExportParams(getTemplateUrl(entity.getTemplateName()));
        params.setHeadingStartRow(entity.getHeadingStartRow());
        params.setSheetName(entity.getSheetName());
        return params;
    }

    /**
     * 获取头部数据
     *
     * @param entity 参数实体
     * @return com.authcode.easyexcel.ExcelUtils.HeaderData
     * @author Mr.yuan
     * @date 2019/8/20 17:19
     **/
    private static HeaderData getHeaderData(ExcelTemplateExportEntity entity) throws IllegalAccessException {
        List headingList = null;
        Object dataObj = entity.getData();
        Field[] fields = dataObj.getClass().getDeclaredFields();
        Map<String, Object> dataMap = new HashMap<>(fields.length);
        for (Field field : fields) {
            field.setAccessible(true);
            String key = field.getName();
            Object value = field.get(dataObj);
            dataMap.put(key, value);
            ExcelDataCollection collection = field.getAnnotation(ExcelDataCollection.class);
            if (collection != null && collection.isSum()) {
                headingList = (List) value;
            }
        }
        return new HeaderData(headingList, dataMap);
    }

    /**
     * 处理合计
     * easypoi 合计默认是保留2位小数，不符合当前需求，因此需要自定义并覆盖
     *
     * @param headingList 标题行数据
     * @param workbook    工作簿
     * @param override    覆盖合计行标记
     * @author Mr.yuan
     * @date 2019/8/20 17:23
     **/
    private static void handleSum(List headingList, Workbook workbook, Boolean override) throws IllegalAccessException {
        if (!CollectionUtils.isEmpty(headingList)) {
            //获取合计列数据
            Map<String, Precision> sumMap = getSumMap(headingList);
            //插入合计列
            addSumColumn(workbook, sumMap, override);
        }
    }

    /**
     * 获取统计列
     *
     * @param headingList 标题行数据
     * @author Mr.yuan
     * @date 2019/8/20 17:29
     **/
    private static Map<String, Precision> getSumMap(List headingList) throws IllegalAccessException {
        Map<String, Precision> sumMap = new HashMap<>(10);
        for (Object obj : headingList) {
            Field[] fields = obj.getClass().getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                Excel excel = field.getAnnotation(Excel.class);
                if (null != excel && excel.isStatistics()) {
                    String colIndex = excel.orderNum();
                    String expression = excel.numFormat();
                    String value = field.get(obj).toString();
                    Precision precision = sumMap.get(colIndex);
                    if (Objects.isNull(precision)) {
                        precision = new Precision();
                        precision.setSum(new BigDecimal(value));
                        precision.setExpression(expression);
                        sumMap.put(colIndex, precision);
                    } else {
                        BigDecimal sum = precision.getSum();
                        precision.setSum(sum.add(new BigDecimal(value)));
                        precision.setExpression(expression);
                        sumMap.put(colIndex, precision);
                    }

                }
            }
        }
        return sumMap;
    }

    /**
     * 插入合计列
     *
     * @param workbook 工作簿
     * @param sumMap   合计数据集
     * @param override 是否覆盖合计
     * @author Mr.yuan
     * @date 2019/8/20 17:33
     **/
    private static void addSumColumn(Workbook workbook, Map<String, Precision> sumMap, boolean override) {
        Sheet sheet = workbook.getSheetAt(0);
        Row sumRow = sheet.createRow(override ? sheet.getLastRowNum() : sheet.getLastRowNum() + 1);
        Cell sumCell = sumRow.createCell(0, CellType.STRING);
        sumCell.setCellStyle(boldCellStyle(workbook));
        sumCell.setCellValue("合计:");
        for (Map.Entry<String, Precision> entry : sumMap.entrySet()) {
            String colIndex = entry.getKey();
            Precision precision = entry.getValue();
            String expression = precision.getExpression();
            if (StringUtils.isEmpty(expression)) {
                sumRow.createCell(Integer.parseInt(colIndex), CellType.NUMERIC).setCellValue(precision.getSum().toString());
            } else {
                int number = expression.contains(".") ? expression.substring(expression.indexOf(".") + 1).length() : 0;
                if (number > 0) {
                    BigDecimal sumValue = precision.getSum().setScale(number, RoundingMode.HALF_UP);
                    sumRow.createCell(Integer.parseInt(colIndex), CellType.NUMERIC).setCellValue(sumValue.toString());
                }
            }
        }
    }

    /**
     * 响应导出
     *
     * @param entity   参数实体
     * @param workbook 工作簿
     * @author Mr.yuan
     * @date 2019/8/20 17:37
     **/
    private static void responseExport(ExcelExportEntity entity, Workbook workbook) throws Exception {
        //模板类型
        String suffix = entity.getType().equals(ExcelType.HSSF) ? ".xls" : ".xlsx";
        HttpServletResponse response = entity.getResponse();
        String fileName = StringUtils.isEmpty(entity.getExportTemplateName()) ? entity.getTemplateName() : entity.getExportTemplateName();
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName+suffix, "UTF-8"));

        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
        workbook.close();
    }


    static class Precision {
        /**
         * 合计值
         */
        private BigDecimal sum;
        /**
         * 精度表达式
         */
        private String expression;

        BigDecimal getSum() {
            return sum;
        }

        void setSum(BigDecimal sum) {
            this.sum = sum;
        }

        String getExpression() {
            return expression;
        }

        void setExpression(String expression) {
            this.expression = expression;
        }
    }

    @Getter
    static class HeaderData {
        /**
         * 表头行数据集
         */
        private List headingList;
        /**
         * 模板数据集
         */
        private Map<String, Object> dataMap;

        HeaderData(List headingList, Map<String, Object> dataMap) {
            this.headingList = headingList;
            this.dataMap = dataMap;
        }
    }

    /**
     * 自定义列样式，此处可根据模板样式扩展
     */
    private static CellStyle boldCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setFont(boldFont(workbook));
        style.setAlignment(HorizontalAlignment.RIGHT);
        return style;
    }

    /**
     * 字体加粗
     */
    private static Font boldFont(Workbook workbook) {
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 13);
        font.setBold(true);
        return font;
    }

    /**
     * 合计列样式
     */
    private static CellStyle sumCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFont(boldFont(workbook));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        return style;
    }
}
